/**
 * 
 * Description: 
 * Copyright (c) 2008-2009  Neo. 
 * All Rights Reserved.
 * @version 1.0  Apr 8, 2009 2:27:19 PM 李萌（neolimeng@gmail.com）created
 */
package org.paradise.dms.poi;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 
 * Description: Copyright (c) 2008-2009 Neo. All Rights Reserved.
 * 
 * @version 1.0 Apr 8, 2009 2:27:19 PM 李萌（neolimeng@gmail.com）created
 */
public class PoiUtil {
	private static Logger log = Logger.getLogger(PoiUtil.class);
	private String[] titleRowValues;
	private String excelFileReadPath = "D:\\";
	private String excelFileWritePath = "D:\\";
	private String excelFileName = "poiTestExel.xls";
	private String excelSheetName = "neosheet";
	private HSSFWorkbook workbook;
	private HSSFSheet sheet;
	private HSSFRow row;
	private HSSFCell cell;
	private HSSFCellStyle titleStyle;
	private OutputStream fileOut;

	public void init(String excelFileName, String excelSheetName) {
		this.excelFileName = excelFileName;
		this.excelSheetName = excelSheetName;
	}

	/**
	 * 
	 * Description:创建标题行
	 * 
	 * @Version1.0 Apr 8, 2009 3:18:20 PM 李萌（neolimeng@gmail.com）创建
	 * @param titleRowValues
	 *            The titles of the Excel file to create.
	 */
	public void poiCreateTitleRow(String[] titleRowValues) {
		row = sheet.createRow(0);
		for (int i = 0; i < titleRowValues.length; i++) {
			cell = row.createCell(i);
			// cell.setCellStyle(titleStyle);
			cell.setCellValue(titleRowValues[i]);
		}
	}

	/**
	 * 
	 * Description:把生成的Excel文件写入制定的输出流中
	 * 
	 * @Version1.0 Apr 8, 2009 3:18:40 PM 李萌（neolimeng@gmail.com）创建
	 * @param workbook
	 *            To write the Excel a HSSFWorkbook is needed.
	 * @param os
	 *            os stands for the destination path of the Excel file.
	 */
	public void poiWriteExcel(HSSFWorkbook workbook, OutputStream os) {
		try {
			workbook.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				os.flush();
				os.close();
				if (workbook != null) {
					workbook = null;
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void poiWriteExcel(String[] titles,List contentList,OutputStream os){
		try{
			HSSFWorkbook workbook=new HSSFWorkbook();
			HSSFSheet sheet=workbook.createSheet();
			
			//Create the titles of the Excel file.
			HSSFRow row=sheet.createRow(0);
			for (int i = 0; i < titles.length; i++) {
				cell = row.createCell(i);
				// cell.setCellStyle(titleStyle);
				cell.setCellValue(titles[i]);
			}
			
			//Write the contents of to the Excel. The contents are contained in the contentList.
			for(int j=0;j<contentList.size();j++){//Rows
				//System.out.print("第"+j+"行数据：");
				row=sheet.createRow(j+1);
				Object[] contents=(Object[])contentList.get(j);
				for(int k=0;k<titles.length;k++){//Each row.
					row.createCell(k).setCellValue(contents[k].toString());
					//System.out.print(contents[k].toString());
				}
				//System.out.println();
			}
			
			//Write the Excel file into the OutputStream
			this.poiWriteExcel(workbook, os);
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Description: Use POI to read a Excel file.
	 * 
	 * The path, the start row, the start column number and the end column
	 * number are needed.
	 * 
	 * The method returns a List which contains many Object[], one of which
	 * stands for one row of data.
	 * 
	 * @Version1.0 Apr 23, 2009 1:42:54 AM 李萌（neolimeng@gmail.com）创建
	 * @param filePath
	 * @param startRow
	 * @param startColumn
	 * @param endColumn
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List poiReadExcel(String filePath, int startRow, int startColumn, int endColumn) {
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		FileInputStream fileIn = null;
		try {
			fileIn = new FileInputStream(filePath);
			fs = new POIFSFileSystem(fileIn);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(0);
			List list = new ArrayList();
			row = sheet.getRow(startRow);
			int k = 0;
			while (row != null && row.getCell(0) != null && row.getCell(0).getStringCellValue() != "") {
				Object[] obj = new Object[endColumn - startColumn + 1];
				for (int i = startColumn, j = 0; i <= endColumn; j++, i++) {
					cell = row.getCell(i);
					obj[j] = cell.getStringCellValue();
				}
				list.add(obj);
				k++;
				row = sheet.getRow(startRow + k);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (null != fileIn) {
				try {
					fileIn.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 
	 * Description: Use POI to read an Excel file.
	 * 
	 * The Excel file path, sheet number, start row number, columns to read are
	 * needed. The method will automatically determine the last row to read.
	 * 
	 * The result List contains to List: The first List contains the corrected
	 * rows of data, where each row of data is contained in a Object[]. The
	 * second List contains the failed row numbers. You can cast the Object of
	 * the List to the row number of int type.
	 * 
	 * @Version1.0 Apr 23, 2009 2:23:52 AM 李萌（neolimeng@gmail.com）创建
	 * @param excelFilePath
	 * @param sheetNumber
	 * @param startRow
	 * @param columnsToRead
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List poiReadExcel(String excelFilePath, int sheetNumber, int startRow, int[] columnsToRead) {
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		FileInputStream fileIn = null;
		try {
			// Read the Excel file and get the "sheetNumber" Excel sheet.
			fileIn = new FileInputStream(excelFilePath);
			fs = new POIFSFileSystem(fileIn);
			wb = new HSSFWorkbook(fs);
			sheet = wb.getSheetAt(sheetNumber);

			// The list where correct results and failed results are saved, and
			// returned to the caller of this method.
			List list = new ArrayList();
			// The list used to save correct results
			List listCorrect = new ArrayList();
			// The list used to save failed results
			List listFailed = new ArrayList();

			// Read the "startRow" row of the Excel sheet.
			row = sheet.getRow(startRow);
			// The counter of rows.
			int k = startRow;
			//
			while (null != row && null != row.getCell(0) && row.getCell(0).getStringCellValue() != "") {// Determine
				// whether
				// we
				// have
				// arrived
				// at
				// the
				// last
				// line.

				// obj stands for a row of data.
				// For each row a new obj is needed, otherwise the former objs
				// in the list is replaced with the later one.
				Object[] obj = new Object[columnsToRead.length];
				// Preassign the data as an empty string.
				for (int j = 0; j < columnsToRead.length; j++) {
					obj[j] = "";
				}

				// Loop to find the first column needed to read.
				int startColumn = 0;
				for (startColumn = 0; startColumn < columnsToRead.length; startColumn++) {
					if (columnsToRead[startColumn] != -1) {// The column marked
						// as "-1" stands
						// for the column
						// not to read.
						break;
					}
				}

				try {
					// Loop to read the data of one line.
					for (int i = startColumn; i < columnsToRead.length; i++) {
						if (columnsToRead[i] != -1) {// if the column is not
							// -1, the data of the
							// column has to be
							// read.
							cell = row.getCell(columnsToRead[i]);
							if (null != cell) {// Check whether the cell is
								// null, if so remain the
								// content as ""
								obj[i] = cell.getStringCellValue();// read the
								// data in
								// the cell
								// as
								// String.
							}
						}
					}

					// If this row of data is read correctly, save the obj to
					// the listCorrect.
					listCorrect.add(obj);

					k++;// read next row of data.
				} catch (Exception e) {
					listFailed.add(k);// save the number of the row where the
					// process of reading data fails.
					k++;// read next row.
					e.printStackTrace();
				}
				row = sheet.getRow(k);
			}
			list.add(listCorrect);// Save the correct rows into the result
			// list.
			list.add(listFailed);// Save the failed row numbers into the
			// result list.
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (null != fileIn) {
				try {
					fileIn.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Description:
	 * 
	 * @Version1.0 Apr 9, 2009 12:43:12 AM 李萌（neolimeng@gmail.com）创建
	 * @param path
	 */
	public void poiReadExcel(String path) {
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path));
			Workbook wb = new HSSFWorkbook(fs);
			Sheet sheet = wb.getSheetAt(0);
			Cell cell = null;

			Row row = sheet.getRow(1);
			cell = row.getCell(0);
			System.out.println(cell.getStringCellValue());
			cell = row.getCell(1);
			System.out.println(cell.getStringCellValue());
			cell = row.getCell(2);
			System.out.println(cell.getStringCellValue());

			row = sheet.getRow(2);
			cell = row.getCell(0);
			System.out.println(cell.getStringCellValue());
			cell = row.getCell(1);
			System.out.println(cell.getStringCellValue());
			cell = row.getCell(2);
			System.out.println(cell.getStringCellValue());
		} catch (Exception e) {
			log.error("DMS_error:读取Excel有误！");
			log.error("DMS_error:错误原因！" + e);
		}
	}

	// getter、setter
	public String[] getTitleRowValues() {
		return titleRowValues;
	}

	public void setTitleRowValues(String[] titleRowValues) {
		this.titleRowValues = titleRowValues;
	}

	public String getExcelFileReadPath() {
		return excelFileReadPath;
	}

	public void setExcelFileReadPath(String excelFileReadPath) {
		this.excelFileReadPath = excelFileReadPath;
	}

	public String getExcelFileWritePath() {
		return excelFileWritePath;
	}

	public void setExcelFileWritePath(String excelFileWritePath) {
		this.excelFileWritePath = excelFileWritePath;
	}

	public String getExcelFileName() {
		return excelFileName;
	}

	public void setExcelFileName(String excelFileName) {
		this.excelFileName = excelFileName;
	}

	public String getExcelSheetName() {
		return excelSheetName;
	}

	public void setExcelSheetName(String excelSheetName) {
		this.excelSheetName = excelSheetName;
	}

	public HSSFWorkbook getWorkbook() {
		if (this.workbook == null) {
			this.workbook = new HSSFWorkbook();
		}
		return workbook;
	}

	public void setWorkbook(HSSFWorkbook workbook) {
		this.workbook = workbook;
	}

	public HSSFSheet getSheet() {
		if (this.sheet == null) {
			this.sheet = this.workbook.createSheet(this.excelSheetName);
		}
		return sheet;
	}

	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}

	public HSSFRow getRow() {
		return row;
	}

	public void setRow(HSSFRow row) {
		this.row = row;
	}

	public HSSFCell getCell() {
		return cell;
	}

	public void setCell(HSSFCell cell) {
		this.cell = cell;
	}

	public HSSFCellStyle getTitleStyle() {
		return titleStyle;
	}

	public void setTitleStyle(HSSFCellStyle titleStyle) {
		this.titleStyle = titleStyle;
	}

	public OutputStream getFileOut() {
		return fileOut;
	}

	public void setFileOut(OutputStream fileOut) {
		this.fileOut = fileOut;
	}
}
